package com.bkfm.framework.core.service;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.RowBounds;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.yak.core.utils.DateUtil;
import org.yak.common.digest.DESUtils;
import org.yak.common.utils.RandomUtil;
import org.yak.core.bean.DBSqlEntity;
import org.yak.core.exception.ServiceException;
import org.yak.core.nosql.MongoDBConfig;
import org.yak.core.provider.ProviderSqlParam;
import org.yak.core.provider.SqlProvider;
import org.yak.core.service.BaseService;
import org.yak.core.store.PageParam;
import org.yak.core.utils.JsonUtils;
import org.yak.core.utils.MapUtils;
import org.yak.core.web.ResultSet;

import com.bkfm.framework.core.api.service.IBkfmTableMetaServer;
import com.bkfm.framework.core.api.store.IBkfmTableMetaStore;
import com.bkfm.framework.core.entity.BkfmDataRecord;
import com.bkfm.framework.core.entity.BkfmFiles;
import com.bkfm.framework.core.entity.BkfmFilter;
import com.bkfm.framework.core.entity.BkfmTableMetaData;
import com.bkfm.framework.core.entity.BkfmTreeModel;
import com.bkfm.framework.core.utils.BkfmTableMetaDataCacheUtils;
import com.bkfm.framework.core.utils.BkfmUtils;
import com.bkfm.framework.core.utils.ExecuteCall;
import com.bkfm.framework.core.utils.QueryCall;
import com.bkfm.framework.sys.utils.BkfmUserUtils;
import com.bkfm.framework.utils.BkfmExpImpColumn;
import com.bkfm.framework.utils.ExcelUtils;
import com.github.pagehelper.PageInfo;

import net.sf.json.JSONObject;

@Service
@Transactional
public class BkfmTableMetaServer extends BaseService<BkfmTableMetaData> implements IBkfmTableMetaServer{

	@Resource
	private IBkfmTableMetaStore tableMetaStore;
	
	/**
	 * 查询所有业务模型数据
	 * @return
	 * @throws Exception
	 */
	public List<BkfmTableMetaData> queryTableMetaAllList()throws Exception{
		return tableMetaStore.selectTableMetaAllList();
	}
	
	/**
	 * 查询业务数据表结构模型
	 * @param tableMetaData
	 * @return
	 * @throws Exception
	 */
	public List<BkfmTableMetaData> queryTableMetaColumn(BkfmFilter bkfmFilter)throws Exception{
		if(StringUtils.isEmpty(bkfmFilter.getObj_path())) {
			throw new ServiceException("业务模型路径不可为空");
		}
		return BkfmTableMetaDataCacheUtils.queryTableMetaColumnList(bkfmFilter);
	}
	
	/**
	 * 查询业务数据记录
	 * @param tableMetaData
	 * @param pageBounds
	 * @return
	 * @throws Exception
	 */
	public PageInfo<Map<String,Object>> queryPageInfoMap(BkfmFilter bkfmFilter,PageParam pageBounds)throws Exception{
		//默认不分页
		RowBounds rowBounds = new RowBounds();
		if(pageBounds.isPaging()){
			//分页
			rowBounds = new RowBounds(pageBounds.getOffset(),pageBounds.getLimit());
		}
		ProviderSqlParam providerSqlParam = new ProviderSqlParam();
		providerSqlParam.setObj_path(bkfmFilter.getObj_path());
		if(!StringUtils.isEmpty(bkfmFilter.getRwid())) {
			providerSqlParam.setFilter(" RWID='"+bkfmFilter.getRwid()+"'");
		}else if(!StringUtils.isEmpty(bkfmFilter.getFilter())){
			providerSqlParam.setFilter(bkfmFilter.getFilter());
		}
		if(!StringUtils.isEmpty(bkfmFilter.getAppendFilter())) {
			if(StringUtils.isEmpty(providerSqlParam.getFilter())) {
				providerSqlParam.setFilter(bkfmFilter.getAppendFilter());
			}else {
				providerSqlParam.setFilter(" AND "+bkfmFilter.getAppendFilter());
			}
		}
		if(!StringUtils.isEmpty(bkfmFilter.getOrderby())) {
			providerSqlParam.setOrder_by(bkfmFilter.getOrderby());
		}
		List<Map<String,Object>> list = tableMetaStore.selectListTableMeta(providerSqlParam, rowBounds);
		PageInfo<Map<String,Object>> page = new PageInfo<Map<String,Object>>(list);
		return page;
	}
	
	/**
	 * 查询业务数据记录
	 * @param tableMetaData
	 * @param pageBounds
	 * @return
	 * @throws Exception
	 */
	public PageInfo<ResultSet> queryPageInfoResultSet(BkfmFilter bkfmFilter,PageParam pageBounds)throws Exception{
		//默认不分页
		RowBounds rowBounds = new RowBounds();
		if(pageBounds.isPaging()){
			//分页
			rowBounds = new RowBounds(pageBounds.getOffset(),pageBounds.getLimit());
		}
		ProviderSqlParam providerSqlParam = new ProviderSqlParam();
		providerSqlParam.setObj_path(bkfmFilter.getObj_path());
		if(!StringUtils.isEmpty(bkfmFilter.getRwid())) {
			providerSqlParam.setFilter(" RWID='"+bkfmFilter.getRwid()+"'");
		}else if(!StringUtils.isEmpty(bkfmFilter.getFilter())){
			providerSqlParam.setFilter(bkfmFilter.getFilter());
		}
		if(!StringUtils.isEmpty(bkfmFilter.getAppendFilter())) {
			if(StringUtils.isEmpty(providerSqlParam.getFilter())) {
				providerSqlParam.setFilter(bkfmFilter.getAppendFilter());
			}else {
				providerSqlParam.setFilter(" AND "+bkfmFilter.getAppendFilter());
			}
		}
		if(!StringUtils.isEmpty(bkfmFilter.getOrderby())) {
			providerSqlParam.setOrder_by(bkfmFilter.getOrderby());
		}
		List<ResultSet> list = tableMetaStore.selectListTableMetaResultSet(providerSqlParam, rowBounds);
		PageInfo<ResultSet> page = new PageInfo<ResultSet>(list);
		return page;
	}
	
	/**
	 * 查询单调记录
	 * 首先根据rwid查询，如果rwid为空则根据filter条件来查询
	 * @param bkfmFilter
	 * @return
	 * @throws Exception
	 */
	public Map<String,Object> queryTableModelMetaData(BkfmFilter bkfmFilter)throws Exception{
		if(StringUtils.isEmpty(bkfmFilter.getObj_path())) {
			throw new ServiceException("无效的业务模型路径");
		}
		ProviderSqlParam providerSqlParam = new ProviderSqlParam();
		providerSqlParam.setObj_path(bkfmFilter.getObj_path());
		if(!StringUtils.isEmpty(bkfmFilter.getRwid())) {
			providerSqlParam.setFilter(" RWID='"+bkfmFilter.getRwid()+"'");
		}else if(!StringUtils.isEmpty(bkfmFilter.getFilter())){
			providerSqlParam.setFilter(bkfmFilter.getFilter());
		}else {
			throw new ServiceException("无效的查询条件");
		}
		return tableMetaStore.selectTableMeta(providerSqlParam);
	}
	
	/**
	 * 查询结果集返回ResultSet对象
	 * @param bkfmFilter
	 * @return
	 * @throws Exception
	 */
	public ResultSet queryTableModelMetaResultSet(BkfmFilter bkfmFilter)throws Exception{
		if(StringUtils.isEmpty(bkfmFilter.getObj_path())) {
			throw new ServiceException("无效的业务模型路径");
		}
		ProviderSqlParam providerSqlParam = new ProviderSqlParam();
		providerSqlParam.setObj_path(bkfmFilter.getObj_path());
		if(!StringUtils.isEmpty(bkfmFilter.getRwid())) {
			providerSqlParam.setFilter(" RWID='"+bkfmFilter.getRwid()+"'");
		}else if(!StringUtils.isEmpty(bkfmFilter.getFilter())){
			providerSqlParam.setFilter(bkfmFilter.getFilter());
		}else {
			throw new ServiceException("无效的查询条件");
		}
		return tableMetaStore.selectTableMetaResultSet(providerSqlParam);
	}
	
	/**
	 * 查询结果集返回ResultSet对象
	 * @param bkfmFilter
	 * @return
	 * @throws Exception
	 */
	public List<ResultSet> queryTableModelMetaResultSetList(BkfmFilter bkfmFilter)throws Exception{
		if(StringUtils.isEmpty(bkfmFilter.getObj_path())) {
			throw new ServiceException("无效的业务模型路径");
		}
		ProviderSqlParam providerSqlParam = new ProviderSqlParam();
		providerSqlParam.setObj_path(bkfmFilter.getObj_path());
		if(!StringUtils.isEmpty(bkfmFilter.getRwid())) {
			providerSqlParam.setFilter(" RWID='"+bkfmFilter.getRwid()+"'");
		}else if(!StringUtils.isEmpty(bkfmFilter.getFilter())){
			providerSqlParam.setFilter(bkfmFilter.getFilter());
		}else {
			throw new ServiceException("无效的查询条件");
		}
		return tableMetaStore.selectTableMetaResultSetList(providerSqlParam);
	}
	
	/**
	 * 保存或者修改数据
	 * @param dataRecord
	 * @throws Exception
	 */
	public void saveModelMetaData(List<BkfmDataRecord> dataRecord)throws Exception{
		if(dataRecord == null || dataRecord.isEmpty()) {
			return;
		}
		for(BkfmDataRecord record : dataRecord) {
			if(BkfmDataRecord.RECORD_STATE_NEW.equals(record.getRecord_state())) {
				//新增
				addModelMetaData(record);
			}else if(BkfmDataRecord.RECORD_STATE_CHANGED.equals(record.getRecord_state())) {
				//修改
				modifyModelMetaData(record);
			}
		}
	}
	
	/**
	 * 新增
	 * @param record
	 * @throws Exception
	 */
	public void addModelMetaData(BkfmDataRecord record) throws Exception{
		ProviderSqlParam param = new ProviderSqlParam();
		Map<String,Object> valueMap = JsonUtils.toMap(record.getRecord_data(), true);
		valueMap.put("RWID", record.getRwid());
		valueMap.put("CREATE_USER_ID", BkfmUserUtils.getCurrentUser().getUser().getRwid());
		valueMap.put("CREATE_USER_NAME", BkfmUserUtils.getCurrentUser().getUser().getUser_no());
		valueMap.put("CREATE_DATE", DateUtil.nowDate());
		valueMap.put("OWNER_USER_ID", BkfmUserUtils.getCurrentUser().getUser().getRwid());
		valueMap.put("OWNER_ORG_ID", BkfmUserUtils.getDefaultOrg().getOrg_rwid());
		param.setTableMetaDataValue(valueMap);
		param.setObj_path(record.getObj_path());
		tableMetaStore.insertTableMeta(param);
	}
	
	/**
	 * 修改
	 * @param record
	 * @throws Exception
	 */
	public void modifyModelMetaData(BkfmDataRecord record) throws Exception{
		ProviderSqlParam param = new ProviderSqlParam();
		Map<String,Object> valueMap = JsonUtils.toMap(record.getRecord_data(), true);
		valueMap.put("RWID", record.getRwid());
		valueMap.put("UPDATE_USER_ID", BkfmUserUtils.getCurrentUser().getUser().getRwid());
		valueMap.put("UPDATE_USER_NAME", BkfmUserUtils.getCurrentUser().getUser().getUser_no());
		valueMap.put("UPDATE_DATE", DateUtil.nowDate());
		param.setTableMetaDataValue(valueMap);
		param.setObj_path(record.getObj_path());
		tableMetaStore.updateTableMeta(param);
	}
	
	/**
	 * 删除
	 * @param record
	 * @throws Exception
	 */
	public void deleteModelMetaData(List<BkfmFilter> bkfmFilterList) throws Exception{
		for(BkfmFilter bkfmFilter : bkfmFilterList) {
			ProviderSqlParam providerSqlParam = new ProviderSqlParam();
			if(!StringUtils.isEmpty(bkfmFilter.getRwid())) {
				providerSqlParam.setFilter(" RWID='"+bkfmFilter.getRwid()+"'");
			}else if(!StringUtils.isEmpty(bkfmFilter.getFilter())){
				providerSqlParam.setFilter(bkfmFilter.getFilter());
			}
			providerSqlParam.setObj_path(bkfmFilter.getObj_path());
			tableMetaStore.deleteTableMeta(providerSqlParam);
		}
	}
	
	/**
	 * 查询下啦选项
	 * @param bkfmFilter
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> queryComboBoxMeta(BkfmFilter bkfmFilter) throws Exception{
		//BkfmTableMetaData metaData = tableMetaStore.selectTableMetaColumn(bkfmFilter);
		BkfmTableMetaData metaData = BkfmTableMetaDataCacheUtils.queryTableMetaColumn(bkfmFilter);
		//获取下拉选项配置
		String selOptions = metaData.getSel_options();
		if(StringUtils.isEmpty(selOptions)){
			return null;
		}
		String Sql = BkfmUtils.parseSelOptions(selOptions);
		if(!StringUtils.isEmpty(bkfmFilter.getFilter())) {
			Sql = "SELECT * FROM ("+Sql+")TS WHERE "+bkfmFilter.getFilter();
		}
		if(!StringUtils.isEmpty(bkfmFilter.getOrderby())) {
			Sql +=" "+bkfmFilter.getOrderby();
		}
		DBSqlEntity sqlParam = new DBSqlEntity();
		sqlParam.setSql(Sql);
		sqlParam.setExecuteType(DBSqlEntity.QUERY);
		return tableMetaStore.selectMapListBySql(sqlParam);
	}
	
	/**
	 * 查询树结构
	 * @param param
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> queryTreeMeta(BkfmTreeModel param)throws Exception{
		DBSqlEntity sqlParam = new DBSqlEntity();
		sqlParam.setSql(param.getTreeSql());
		sqlParam.setExecuteType(DBSqlEntity.QUERY);
		List<Map<String,Object>> list = tableMetaStore.selectMapListBySql(sqlParam);
		List<Map<String,Object>> treelist = new ArrayList<Map<String,Object>>();
		for(Map<String,Object> map : list) {
			//转换小写
			treelist.add(MapUtils.toLowerMap(map));
		}
		return treelist;
	}

	/**
	 * 根据输入的sql查询结果集
	 * @param sql sql语句
	 * @param queryCall 回调方法
	 * @throws Exception
	 */
	public List<ResultSet> queryResultBySql(String sql,QueryCall queryCall) throws Exception{
		DBSqlEntity sqlParam = new DBSqlEntity();
		sqlParam.setSql(sql);
		sqlParam.setExecuteType(DBSqlEntity.QUERY);
		List<ResultSet> list = tableMetaStore.selectResultSetListBySql(sqlParam);
		if(queryCall !=null){
			queryCall.callBackList(list);
		}
		return list;
	}
	
	/**
	 * 执行单调sql语句
	 * @param sql
	 * @param executeCall
	 * @return
	 * @throws Exception
	 */
	public int executeSql(String sql,ExecuteCall executeCall)throws Exception{
		DBSqlEntity sqlParam = new DBSqlEntity();
		sqlParam.setSql(DESUtils.decryption(sql));
		sqlParam.setExecuteType(DBSqlEntity.EXECUTE);
		int i = tableMetaStore.updateTableBySql(sqlParam);
		if(executeCall !=null){
			executeCall.callExecute(tableMetaStore);
		}
		return i;
	}
	
	/**
	 * 批量执行sql
	 * @param sql
	 * @param executeCall
	 * @throws Exception
	 */
	public void executeBatchSql(List<String> sqllist,ExecuteCall executeCall)throws Exception{
		for(String sql : sqllist) {
			DBSqlEntity sqlParam = new DBSqlEntity();
			sqlParam.setSql(DESUtils.decryption(sql));
			sqlParam.setExecuteType(DBSqlEntity.EXECUTE);
			tableMetaStore.updateTableBySql(sqlParam);
		}
		if(executeCall !=null){
			executeCall.callExecute(tableMetaStore);
		}
	}
	
	/**
	 * 检查记录数
	 * @param bkfmFilter
	 * @return
	 * @throws Exception
	 */
	public int checkNumber(BkfmFilter bkfmFilter)throws Exception{
		String table_name = bkfmFilter.getObj_path().substring(bkfmFilter.getObj_path().lastIndexOf("/")+1).toUpperCase();
		StringBuffer sql = new StringBuffer("SELECT COUNT(0) AS COUNT FROM "+table_name+" ");
		if(!StringUtils.isEmpty(bkfmFilter.getFilter())) {
			sql.append(" WHERE ").append(bkfmFilter.getFilter());
		}
		DBSqlEntity sqlParam = new DBSqlEntity();
		sqlParam.setSql(sql.toString());
		sqlParam.setExecuteType(DBSqlEntity.QUERY);
		return tableMetaStore.selectCountBySql(sqlParam);
	}
	
	/**
	 * 导出excel文件,返回下载文件名称
	 * @param bkfmFilter
	 * @param expColumnList
	 * @throws Exception
	 */
	public String exportData(BkfmFilter bkfmFilter,List<String> expColumnList)throws Exception{
		//查询导出表头
		Map<String,Object> queryFilter = new HashMap<String,Object>();
		queryFilter.put("obj_path", bkfmFilter.getObj_path());
		List<Map<String,Object>> fields = MongoDBConfig.queryMapList(SqlProvider.MONGODB_NAME, SqlProvider.MONGODB_TABLEMETADATA_NAME, queryFilter);
		if(fields == null || fields.isEmpty()) {
			return null;
		}
		Map<String,Object> param = fields.get(0);
		queryFilter.clear();
		queryFilter.put("father_tablemetadata_id", param.get("TABLEMETADATA_ID"));
		fields = MongoDBConfig.queryMapList(SqlProvider.MONGODB_NAME, SqlProvider.MONGODB_TABLEMETADATA_NAME, queryFilter);
		if(fields == null || fields.isEmpty()) {
			return null;
		}
		
		
		//生成需要导出的表头
		List<BkfmExpImpColumn> tableHeaderColumnList = new ArrayList<BkfmExpImpColumn>();
		for(String columnField : expColumnList) {
			for(Map<String,Object> columnMap : fields) {
				String field_column = String.valueOf(columnMap.get("EN_US"));
				if(columnField.endsWith("_DISPLAYVALUE")) {
					BkfmExpImpColumn bkfmColumn = new BkfmExpImpColumn();
					bkfmColumn.setField_label(String.valueOf(columnMap.get("ZH_CN"))+"_编码");
					bkfmColumn.setField_name(columnField);
					tableHeaderColumnList.add(bkfmColumn);
					break;
				}else {
					if(columnField.equals(field_column)) {
						BkfmExpImpColumn bkfmColumn = new BkfmExpImpColumn();
						bkfmColumn.setField_label(String.valueOf(columnMap.get("ZH_CN")));
						bkfmColumn.setField_name(columnField);
						tableHeaderColumnList.add(bkfmColumn);
						break;
					}
				}
			}
		}
		
		//查询数据
		PageParam pageBounds = new PageParam();
		pageBounds.setPaging(false);
		PageInfo<Map<String,Object>> listResult = queryPageInfoMap(bkfmFilter,pageBounds);
		if(listResult ==null || listResult.getList().isEmpty()) {
			throw new ServiceException("导出文件失败，没有查询到需要导出的数据");
		}
		//导出excel文件
		String exp_file_path = ExcelUtils.exportExcelFile("", tableHeaderColumnList, listResult.getList());
		//返回excel文件路径	
		return DESUtils.encryption(exp_file_path, DESUtils.SECRETKEY);
	}
	
	/**
	 * 导入数据
	 * @param bkfmFilter
	 * @param impColumnList
	 * @param importFile
	 * @return
	 * @throws Exception
	 */
	public void importData(BkfmFilter bkfmFilter,List<BkfmExpImpColumn> impColumnList,File importFile)throws Exception{
		//读取excel表头
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(importFile));
        //读取excel模板
		HSSFWorkbook wb = new HSSFWorkbook(fs);
        //读取了模板内所有sheet内容
        HSSFSheet sheet = wb.getSheetAt(0);
        wb.close();
        //获取总记录行
        int totalRowCount = sheet.getLastRowNum();
        //剔除表头，从第二行数据行开始读取
        for(int rownum=1;rownum<totalRowCount;rownum++) {
        	HSSFRow xssfRow = sheet.getRow(rownum);
        	JSONObject valueJson = new JSONObject();
        	for(BkfmExpImpColumn column : impColumnList) {
        		//设置单元格类型
        		xssfRow.getCell(column.getField_excel_head_col()).setCellType(Cell.CELL_TYPE_STRING);
        		//获取单元格值
        		valueJson.put(column.getField_name(), xssfRow.getCell(column.getField_excel_head_col()).getStringCellValue());
        	}
        	if(valueJson.isEmpty()) {
        		continue;
        	}
        	BkfmDataRecord record = new BkfmDataRecord();
        	record.setObj_path(bkfmFilter.getObj_path());
        	record.setRecord_data(valueJson.toString());
        	record.setRwid(RandomUtil.getRandom());
        	addModelMetaData(record);
        }
	}
	
	
	/**
	 * 保存上传的附件
	 * @param fileList
	 * @throws Exception
	 */
	public void uploadFile(List<BkfmFiles> bkfmFilesList)throws Exception{
		for(BkfmFiles bkfmFile : bkfmFilesList) {
			BkfmDataRecord record = new BkfmDataRecord();
			record.setObj_path("ROOT/XTGLPT/JCSJGL/WJGL/BLK_FILES");
			record.setRecord_data(JSONObject.fromObject(bkfmFile).toString());
			record.setRecord_state(BkfmDataRecord.RECORD_STATE_NEW);
			record.setRwid(RandomUtil.getRandom());
			//保存
			addModelMetaData(record);
		}
	}
}
